Query Designer

In This Topic...

The Query Designer allows custom queries to be created directly from system data. The designer provides full control over data columns, aggregate functions, filtering, grouping, and sorting. Queries can be saved for future use, and modified as necessary.

Query Editor Panel

The Query Editor panel opens to the right of the screen when selecting Query from the Dashboard group. Click X to close the panel again.

The options are divided into groups, depending on which feature opened the panel.

New Query

This group is used to create new custom queries.

To create a new custom query, select a Topic and click Create Query. The available topics are custom to your SmartView implementation. For details on the purpose and usage of each topic, please contact your system administrator.

On creating a new query, the Query Designer window opens.

User Queries

This group is used to open an existing custom query saved in the system.

The Show All switch currently has no effect.

Click a name to open the query. The Query Designer window opens.

Query Designer Window

The main screen of the Query Designer tool is used to manage the selected columns, criteria, and sorting.

The window is composed of three sections, Column Management, Criteria Management, and Sorting Controls. The window can be minimized, maximized (the default appearance), and resized with the standard controls. Click X to close the window.

Click Save to open the Save Query panel.

Click Execute to view the Query Results.

Column Management

The column management section is used to select the columns to be included in the query, and define any aggregate functions.

At the top of the section, configure how many records should be included in the query.

Show following columns for ... records
All	Includes all records. This option does not require any additional settings.
First	Only displays a certain number of top values. This option displays the Number, Type, and Ties fields.
Last	Only displays a certain number of lowest values. This option displays the Number, Type, and Ties fields.
Number	Enter the number of records to display.
Type	Select if the Number field represents the number of Records or the percentage % of the total records.
Ties	When showing the first or last records in the query, there may be multiple records tied for the position at the cut-off point. This setting determines how those ties are handled. A sorted column is required to use this feature.
With Ties	The results are expanded to include the extra tied records.
Without Ties	Only the specified Number of records are included. The records that are kept are determined by the configured sort order.

The column detail panels display the settings for each column included in the query. Click the Append Column icon to add a column.

Click [enter field] to select a column from those available in the topic.

Aggregate functions can be applied in the ƒx field. Some functions might not be available if they cannot be applied to the current data type.

None	No function is applied.
Average	Displays the average value of the available records.
Count	Displays the number of available records.
Maximum	Displays the highest value of the available records.
Minimum	Displays the lowest value of the available records.
Sum	Displays the sum of all available records.
Count Distinct	Displays the number of unique available records.

When hovering the mouse over a column detail panel, a control bar appears near the top of the panel.

	Move Left	Moves the column to the left.
	Remove	Removes the column.
	Insert	Inserts a new column detail panel to the left.
	Update Visibility	Hides the column from the query display. The column data is still available for use in filters and grouping.
	Move Right	Moves the column to the right.

Record Criteria

Criteria can be applied to restrict the records included in the query.

At the top of the section, the Where field defines how the criteria should be applied.

All	All criteria must be met or the record is not included.
Any	The record is included if it meets at least one of the criteria.
None	The record is only included if it does not meet any of the criteria.
Not All	The record is included if at least one criteria is not met.
Some Not All	The record is included if at least one criteria is met and at least one criteria is not met.

To add filter criteria, click the Add Criteria icon to add a top-level criteria, or click the Add Group icon to add a group of criteria. The criteria structure is displayed in the window.

Click Enter Field to select the field for the comparison. Once a field has been selected, click the field name to select a different field.

Click the second field to select the comparison type, and provide any necessary comparison values. Some comparison types may not be available due to the data type.

Less	Checks if the field value is less than one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be less than Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Less or equal	Checks if the field value is less than or equal to one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be less than or equal to Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Equal	Checks if the field value is equal to a provided value.
	Click Enter Value to provide the comparison value.
Not equal	Checks if the field value is not equal to a provided value.
	Click Enter Value to provide the comparison value.
Greater	Checks if the field value is greater than one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be greater than Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Greater or equal	Checks if the field value is greater than or equal to one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be greater than or equal to Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Is empty	Checks if the field is empty. No other configuration is necessary for this comparison.
Is not empty	Checks if the field is not empty. No other configuration is necessary for this comparison.
Between	Checks if the field value is between two provided values.
	Click Enter Value to provide each comparison value.
Not between	Checks if the field value is not between two provided values.
	Click Enter Value to provide each comparison value.
In list	Checks if the field value matches one or more provided values.
	Click Enter Value to provide each comparison value.
	Select if the field value must match Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Not in list	Checks if the field value does not match one or more provided values.
	Click Enter Value to provide each comparison value.
	Select if the field value must not match Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .

Any additional criteria are marked with And or Or, depending on the Select records where setting. To create criteria with different conditions, criteria groups can be added.

Each criteria group is managed the same as the top-level criteria, starting with the Select records where field and controls to add criteria and additional criteria groups (visible when hovering over the group header), plus an additional icon to delete the current criteria group.

The individual criteria in criteria groups are defined the same way as the criteria in the top level.

Groups can be added within criteria groups to provide even more detailed combinations.

For each criteria entry, including those in criteria groups, the Toggle Criteria icon can be used to temporarily disable the criteria, while the Remove Criteria icon deletes the criteria entirely.

Click Group Criteria to display the Group Criteria section.

Group Criteria

Criteria can be applied to restrict groups of data (aggregates) included in the query. The grouping is calculated after the Record Criteria have been applied.

The group criteria section is displayed by clicking Group Criteria in the Record Criteria section. Click Close to close the group criteria section.

At the top of the section, the Where field defines how the criteria should be applied.

All	All criteria must be met or the group is not included.
Any	The group is included if it meets at least one of the criteria.
None	The group is only included if it does not meet any of the criteria.
Not All	The group is included if at least one criteria is not met.
Some Not All	The group is included if at least one criteria is met and at least one criteria is not met.

To add filter criteria, click the Add Criteria icon to add a top-level criteria, or click the Add Group icon to add a group of criteria. The criteria structure is displayed in the window.

Select the aggregate type for the comparison field.

Average	Determines the average value of the available records.
Count	Determines the number of available records.
Maximum	Determines the highest value of the available records.
Minimum	Determines the lowest value of the available records.
Sum	Determines the sum of all available records.
Count Distinct	Determines the number of unique available records.

Click Enter Field to select the field for the comparison. Once a field has been selected, click the field name to select a different field.

Click the third field to select the comparison type, and provide any necessary comparison values. Some comparison types may not be available due to the data type.

Less	Checks if the group aggregate value is less than one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be less than Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Less or equal	Checks if the group aggregate value is less than or equal to one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be less than or equal to Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Equal	Checks if the group aggregate value is equal to a provided value.
	Click Enter Value to provide the comparison value.
Not equal	Checks if the group aggregate value is not equal to a provided value.
	Click Enter Value to provide the comparison value.
Greater	Checks if the group aggregate value is greater than one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be greater than Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Greater or equal	Checks if the group aggregate value is greater than or equal to one or more provided values.
	Click Enter Value to provide the comparison value.
	If multiple values are specified, select if the field value must be greater than or equal to Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Is empty	Checks if the group aggregate value is empty. No other configuration is necessary for this comparison.
Is not empty	Checks if the group aggregate value is not empty. No other configuration is necessary for this comparison.
Between	Checks if the group aggregate value is between two provided values.
	Click Enter Value to provide each comparison value.
Not between	Checks if the group aggregate value is not between two provided values.
	Click Enter Value to provide each comparison value.
In list	Checks if the group aggregate value matches one or more provided values.
	Click Enter Value to provide each comparison value.
	Select if the field value must match Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .
Not in list	Checks if the group aggregate value does not match one or more provided values.
	Click Enter Value to provide each comparison value.
	Select if the field value must not match Any comparison value, or All comparison values.
	Click the Add Value icon to add additional comparison values. To delete a comparison value, hover over the value and click the Delete icon .

Any additional criteria are marked with And or Or, depending on the Select groups where setting. To create criteria with different conditions, criteria groups can be added.

Each criteria group is managed the same as the top-level criteria, starting with the Select records where field and controls to add criteria and criteria groups (visible when hovering over the group header), plus an additional icon to delete the current criteria group.

The individual criteria in criteria groups are defined the same way as the criteria in the top level.

Groups can be added within criteria groups to provide even more detailed combinations.

Sorting Controls

The results can be sorted by fields and aggregate values of fields.

One field is available by default, but additional fields can be added using the Add Order icon .

For each field, select if the sorting should be Ascending or Descending .

If the sorting should be based on an aggregate value, select the type of aggregate.

None	No aggregate is applied.
Average	Sorts according to the average value of the available records.
Count	Sorts according to the number of available records.
Maximum	Sorts according to the highest value of the available records.
Minimum	Sorts according to the lowest value of the available records.
Sum	Sorts according to the sum of all available records.
Count Distinct	Sorts according to the number of unique available records.

Click Enter Field to select the field for sorting. To delete a field, hover over the field name and click the Delete icon .

Save Query Panel

A custom query can be saved with a specific configuration. When using the query, settings can be changed for the specific requirements at the time. The changes are not saved unless the query is saved.

The Save Query panel opens to the right of the screen when selecting Save in the Query Designer window. Click X to close the panel again.

Name	The name of the query can be edited.
Description	The description of the query can be edited.
Topic	The topic name can be edited.
Cache	Enter the time, in seconds, that the query results should be kept by the system. During this time, the system will return the results from the most recent query. After that time, the system will run a new query. Using cached queries can improve performance for large queries.
No records message	Enter a message to be displayed if no records are available. If no message is specified, the default message "No records to display" is used.
	This message is inherited at the dataview level in the dataview configuration, but can be overridden. However, if this message is changed here, any dataviews that already have custom messages will not be updated.
Create Data View	The query can be made available when designing the dashboard. This allows it to be assigned to a cell in the dashboard.
	Note that any changes made to the query in the future will not affect the version in the dashboard unless this option is enabled again when saving the changes.
Publish to SSRS	This feature is not in use at this time.

Click Save to save the query.

Query Results Window

When executing a query, the Query Results window opens. This window displays the results with the current query configuration, and provides controls for sorting, filtering, and changing the view mode.

Columns can be sorted by clicking on the header, and filters can be applied using the Filter icon .

The Control icon to the left of query name opens the control panel, where the query can be refreshed, changed to a different view mode, or exported to an Excel file.

Click Edit Query to return to the Query Designer window.